package groups.common.dao;

/*
 * Criado por: Fernando Lener de Arruda
 */

import groups.common.utils.StringUtils;
import groups.diseases.Disease;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;


public class SQLTable{

	private String tableName;
	private Connection connection;
	private Class<?> tableClass;
	private Vector<ColumnMethod> primaryKeyColumns;
	private ColumnMethod autoIncrementColumn;
	private Vector<ColumnMethod> columns;
	private String insertStatement;
	private String updateStatement;
	private String selectStatement;
	private String deleteStatement;
	
	public SQLTable(Class<?> tableClass,String tableName, Connection connection){
		this.tableName = tableName;
		this.connection = connection;
		this.tableClass = tableClass;
		columns = new Vector<SQLTable.ColumnMethod>();
		primaryKeyColumns = new Vector<SQLTable.ColumnMethod>();
		autoIncrementColumn = null;
		
		updateStatement = "update "+tableName;
		deleteStatement = "delete from "+tableName;
	}
	
	//Nota: Adicione todos os metodos get/set da classe na ordem
	// em que aparecem no construtor!
	public void addColumnMethod(String columnName, String getMethod, boolean auto_increment,boolean composesPK)
			throws SecurityException, NoSuchMethodException{
		
		Method getM = tableClass.getMethod(getMethod);
		
		ColumnMethod m = new ColumnMethod(getM, columnName); 
		columns.add(m);
		
		if(auto_increment)
			autoIncrementColumn = m;
		
		if(composesPK)
			primaryKeyColumns.add(m);
		
		updateInsertStatement();
		updateSelectStatement();
	}
	
	public Object create(Object obj) 
			throws IllegalArgumentException, IllegalAccessException,
			InvocationTargetException, SQLException, SecurityException, InstantiationException{
		
		String query = createInsertStatement(obj);
		Statement stm = connection.createStatement();
		
		stm.executeUpdate(query,Statement.RETURN_GENERATED_KEYS);
		
		ResultSet set = stm.getGeneratedKeys();
		
		//Se foi gerado id
		if(set.next()){
			int id = set.getInt(1);
//			obj.getClass()
			Object fields[] = getFieldValues(obj);
			
			//Espera-se que sempre seja achado um autoIncrement
			//senão o id não seria gerado...
			for (int i = 0; i < fields.length; i++) {
				if(columns.elementAt(i)==autoIncrementColumn){
					fields[i] = id;
					return createObjectFromFields(fields);
				}
					
			}
			
		}
		else{
			//Clone
			return createObjectFromFields(getFieldValues(obj));
		}
		
			
		return null;
	}
	
	public boolean deleteExact(Object obj) 
			throws IllegalArgumentException, IllegalAccessException, InvocationTargetException, SQLException{
		String query = createDeleteStatement(obj, " and ");
		
		Statement stm = connection.createStatement();
		
		if(stm.executeUpdate(query)==0){
			return false;
		}
		
		return true;
	}
	
	public int deleteAny(Object obj) throws SQLException, IllegalArgumentException,
		IllegalAccessException, InvocationTargetException{
		
		String query = createDeleteStatement(obj, " or ");
		
		Statement stm = connection.createStatement();
		
		return stm.executeUpdate(query);
	}
	
	public Object read(Object obj) throws IllegalArgumentException, SecurityException,
	InstantiationException, IllegalAccessException, InvocationTargetException, SQLException{
		String query = createSelectStatement(obj, " and ");
		
		Statement stm = connection.createStatement();
		
		ResultSet set = stm.executeQuery(query);
		
		if(set.next())
			return createObjectFromFields(getFieldValues(set));
		else
			return null;
	}
	
	public Object searchExact(Object obj)
			throws IllegalArgumentException, IllegalAccessException, InvocationTargetException, SQLException, SecurityException, InstantiationException{
		String query = createSelectStatement(obj, " and ");
		
		Statement stm = connection.createStatement();
		
		ResultSet set = stm.executeQuery(query);
		
		if(set.next())
			return createObjectFromFields(getFieldValues(set));
		
		return null;
	}
	
	public Vector<Object> searchAny(Object obj)
			throws IllegalArgumentException, IllegalAccessException, InvocationTargetException,
			SQLException, SecurityException, InstantiationException{
		Vector<Object> res = new Vector<Object>();
		
		String query = createSelectStatement(obj, " or ");
		
		Statement stm = connection.createStatement();
		
		ResultSet set = stm.executeQuery(query);
		
		while(set.next())
			res.add(createObjectFromFields(getFieldValues(set)));
		
		return res;
	}
	
	public Vector<Object> readAll(Object obj) throws IllegalArgumentException, IllegalAccessException,
	InvocationTargetException, SQLException, SecurityException, InstantiationException{
		Vector<Object> res = new Vector<Object>();
		
		String query = createSelectStatement(obj, null);
		
		Statement stm = connection.createStatement();
		
		ResultSet set = stm.executeQuery(query);
		
		while(set.next())
			res.add(createObjectFromFields(getFieldValues(set)));
		
		return res;
	}
	
	
	public boolean update(Object obj) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException, SQLException{
		String query = createUpdateStatement(obj);

		Statement stm = connection.createStatement();
		
		return stm.executeUpdate(query)==1;
	}
	
	
	
	
	private void updateInsertStatement(){
		Vector<String> strs = new Vector<String>();
		
		if(columns.size()==0)
			return;
		
		for (ColumnMethod cm : columns) {
			if(cm!=autoIncrementColumn)
				strs.add(cm.getTableColumn());
		}
		
		insertStatement = String.format("insert into %s (%s) values(%%s)", tableName, StringUtils.join(strs.toArray(), ","));
		
	}
	
	private void updateSelectStatement(){
		Vector<String> strs = new Vector<String>();
		
		if(columns.size()==0)
			return;
		
		for (ColumnMethod cm : columns) {
			strs.add(cm.getTableColumn());
		}
		
		selectStatement = String.format("select %s from %s",  StringUtils.join(strs.toArray(),","),tableName);
	}
	
	
	private String createInsertStatement(Object obj)
			throws IllegalArgumentException, IllegalAccessException, InvocationTargetException{
		
		Vector<String> strs = new Vector<String>();
		
		for (ColumnMethod method : columns) {
			if(method!=autoIncrementColumn)
				strs.add(objectToSqlString(method.getGetMethod().invoke(obj)));
		}
		
		return String.format(insertStatement, StringUtils.join(strs.toArray(), ","));
	}
	
	protected String createSelectStatement(Object obj, String connector) 
			throws IllegalArgumentException, IllegalAccessException, InvocationTargetException{
		
		if(obj==null)
			return selectStatement;
		
		Vector<String> strs = new Vector<String>();
		
		for (ColumnMethod method : columns) {
			strs.add(method.getTableColumn()+"=" + objectToSqlString(method.getGetMethod().invoke(obj)) );
		}
		
		return selectStatement + " where "+ StringUtils.join(strs.toArray(), connector);
	}
	
	protected String createDeleteStatement(Object obj,String connector)
			throws IllegalArgumentException, IllegalAccessException, InvocationTargetException{
		Vector<String> strs = new Vector<String>();
		
		for (ColumnMethod method : columns) {
			if(method==autoIncrementColumn)
				strs.add(method.getTableColumn()+"=" + objectToSqlString(method.getGetMethod().invoke(obj)));
		}
		
		return deleteStatement+ (obj==null? "":(" where " + StringUtils.join(strs.toArray(), connector)));
	}
	
	protected String createUpdateStatement(Object obj)
			throws IllegalArgumentException, IllegalAccessException, InvocationTargetException{
		Vector<String> keys = new Vector<String>();
		Vector<String> nonKeys = new Vector<String>();
		
		for (ColumnMethod method : columns) {
			if(primaryKeyColumns.contains(method))
				keys.add(method.getTableColumn()+"=" + objectToSqlString(method.getGetMethod().invoke(obj)));
			else if(method!=autoIncrementColumn)
				nonKeys.add(method.getTableColumn()+"="+objectToSqlString(method.getGetMethod().invoke(obj)));
		}
		
		return updateStatement+ (obj==null? 
				""
				:
			" set "+ StringUtils.join(nonKeys.toArray(), ", ") +	" where "
		+ StringUtils.join(keys.toArray(), " and "));
	}
	
	private Object createObjectFromFields(Object... fields)
			throws InstantiationException, IllegalAccessException,
			IllegalArgumentException, SecurityException, InvocationTargetException{
		return tableClass.getConstructors()[0].newInstance(fields);
	}
	
	private Object[] getFieldValues(Object obj)
			throws IllegalArgumentException, IllegalAccessException, InvocationTargetException{
		Vector<Object> values = new Vector<Object>();
		
		for (ColumnMethod m : columns) {
			values.add(m.getGetMethod().invoke(obj));
		}
		
		return values.toArray();
	}
	
	private Object[] getFieldValues(ResultSet set) throws SQLException{
		Vector<Object> values = new Vector<Object>();

		for (ColumnMethod m : columns) {
			values.add(set.getObject(m.getTableColumn()));
		}
		
		return values.toArray();
	}
	
	private String objectToSqlString(Object obj){
		if(obj.getClass()==String.class)
			return "'"+obj.toString()+"'";
		else
			return obj.toString();
	}
	
	public static void main(String[] args){

		try {
			SQLTable table = new SQLTable(Disease.class, "Disease", null);
			

			table.addColumnMethod("idDisease", 	"getId",		true,	true);
			table.addColumnMethod("name", 		"getName",		false, false);
			table.addColumnMethod("endemic",	"isEndemic" ,	false, false);
			
			Disease d = new Disease(1,"Teste",true);
			System.out.println(table.createInsertStatement(d));
			System.out.println(table.createDeleteStatement(d," or "));
			System.out.println(table.createSelectStatement(d," and "));
			System.out.println(table.createUpdateStatement(d));
			
			Disease usr=(Disease)table.createObjectFromFields(1,"teste",false);
			
			usr.getId();
			
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (NoSuchMethodException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		}
		
		
	}
	/*
	 * Classe auxiliar da SQLTable
	 */
	
	private static class ColumnMethod{
		private Method getMethod;
		private String tableColumn;
		
		public ColumnMethod(Method getMethod,String tableColumn) {
			this.getMethod = getMethod;
			this.tableColumn = tableColumn;
		}

		public Method getGetMethod() {
			return getMethod;
		}

		public String getTableColumn() {
			return tableColumn;
		}


	}
	

}
